package com.lx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import com.lx.entity.Resume;
import com.lx.utils.DBUtils;

public class ResumeDao {
		public Resume getApplicantID(String email){
			Resume re = new Resume();
			Connection conn =null;
			ResultSet rs =null;
			PreparedStatement ps = null;
			String sql = "SELECT * FROM TB_RESUME_BASICINFO WHERE APPLICANT_ID = (SELECT APPLICANT_ID FROM TB_APPLICANT WHERE APPLICANT_EMAIL=?)";
			try{
				
				conn = DBUtils.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setString(1, email);
				rs = ps.executeQuery();
				while(rs.next()){
					re.setBasicinfo_id(rs.getInt(1));
					re.setApplicant_id(rs.getInt(2));
					re.setRealname(rs.getString(3));
					re.setGender(rs.getString(4));
					re.setBirthday(rs.getTimestamp(5));
					re.setCurrent_loc(rs.getString(6));
					re.setResident_loc(rs.getString(7));
					re.setTelephone(rs.getString(8));
					re.setEmail(rs.getString(9));
					re.setJob_intension(rs.getString(10));
					re.setJob_experience(rs.getString(11));
					re.setHead_shot(rs.getString(12));
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBUtils.close(conn, rs, null, ps);
			}
			return re;
		}
		
		public int add(Resume resum,int applicant_id){
			int basecinfo_id = 0;
			Connection conn =null;
			ResultSet rs =null;
			PreparedStatement ps = null;
			String sql = "insert into tb_RESUME_BASICINFO(realname,gender,birthday,telephone,"
					+ "email,job_intension,job_experience,applicant_id) values(?,?,?,?,?,?,?,?)";
			String 	sql1 = "select basicinfo_id from tb_RESUME_BASICINFO where applicant_id=?";
			try{
				conn = DBUtils.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setString(1, resum.getRealname());
				ps.setString(2, resum.getGender());
				ps.setTimestamp(3, resum.getBirthday()==null?null:new Timestamp(resum.getBirthday().getTime()));
				ps.setString(4, resum.getTelephone());
				ps.setString(5, resum.getEmail());
				ps.setString(6, resum.getJob_intension());
				ps.setString(7, resum.getJob_experience());
				ps.setInt(8, applicant_id);
				ps.executeUpdate();
				
				ps = conn.prepareStatement(sql1);
				ps.setString(1,String.valueOf(applicant_id));
				rs = ps.executeQuery();
				if(rs.next()){
					basecinfo_id = rs.getInt(1);
				}
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBUtils.close(conn, rs, null, ps);
			}
			return basecinfo_id;
			
		}

		public Resume queryBasec(int basecinfoid){
			Resume re = new Resume();
			Connection conn =null;
			ResultSet rs =null;
			PreparedStatement ps = null;
			String sql = "SELECT * FROM TB_RESUME_BASICINFO WHERE Basicinfo_id=?";
			try{
				
				conn = DBUtils.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, basecinfoid);
				rs = ps.executeQuery();
				while(rs.next()){
					re.setBasicinfo_id(rs.getInt(1));
					re.setApplicant_id(rs.getInt(2));
					re.setRealname(rs.getString(3));
					re.setGender(rs.getString(4));
					re.setBirthday(rs.getTimestamp(5));
					re.setCurrent_loc(rs.getString(6));
					re.setResident_loc(rs.getString(7));
					re.setTelephone(rs.getString(8));
					re.setEmail(rs.getString(9));
					re.setJob_intension(rs.getString(10));
					re.setJob_experience(rs.getString(11));
					re.setHead_shot(rs.getString(12));
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBUtils.close(conn, rs, null, ps);
			}
			return re;
		}
		
		public Resume queryBasecid(int basecinfoid){
			Resume re = new Resume();
			Connection conn =null;
			ResultSet rs =null;
			PreparedStatement ps = null;
			String sql = "SELECT * FROM TB_RESUME_BASICINFO WHERE Basicinfo_id=?";
			try{
				
				conn = DBUtils.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, basecinfoid);
				rs = ps.executeQuery();
				while(rs.next()){
					re.setBasicinfo_id(rs.getInt(1));
					re.setApplicant_id(rs.getInt(2));
					re.setRealname(rs.getString(3));
					re.setGender(rs.getString(4));
					re.setBirthday(rs.getTimestamp(5));
					re.setCurrent_loc(rs.getString(6));
					re.setResident_loc(rs.getString(7));
					re.setTelephone(rs.getString(8));
					re.setEmail(rs.getString(9));
					re.setJob_intension(rs.getString(10));
					re.setJob_experience(rs.getString(11));
					re.setHead_shot(rs.getString(12));
				}
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBUtils.close(conn, rs, null, ps);
			}
			return re;
		}
		
		public void queryBasecUpdate(Resume resume,int basecinfoid){
			Resume re = new Resume();
			Connection conn =null;
			ResultSet rs =null;
			PreparedStatement ps = null;
			String sql = "update TB_RESUME_BASICINFO set realname=?,gender=?,birthday=?,telephone=?,"
					+ "email=?,job_intension=?,job_experience=? where basicinfo_id=?";
			try{
				conn = DBUtils.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setString(1, resume.getRealname());
				ps.setString(2, resume.getGender());
				ps.setTimestamp(3, resume.getBirthday()==null?null:new Timestamp(resume.getBirthday().getTime()));
				ps.setString(4, resume.getTelephone());
				ps.setString(5, resume.getEmail());
				ps.setString(6, resume.getJob_intension());
				ps.setString(7, resume.getJob_experience());
				ps.setInt(8, basecinfoid);
				ps.executeUpdate();
				
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBUtils.close(conn, rs, null, ps);
			}
		}
	
		public void updateHeadShot(int basicinfoid,String newFileName){
			Connection conn =null;
			ResultSet rs =null;
			PreparedStatement ps = null;
			String sql = "update TB_RESUME_BASICINFO set head_shot=? where basicinfo_id=?";
			try{
				conn = DBUtils.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setString(1,  newFileName);
				ps.setInt(2,basicinfoid);
				ps.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				DBUtils.close(conn, rs, null, ps);
			}
		}
		
		
}
